package com.eling.elcms.travel.dao.hibernate;

import com.eling.elcms.basedoc.model.PersonalInfo;
import com.eling.elcms.core.AppContext;
import com.eling.elcms.core.dao.hibernate.GenericDaoHibernate;
import com.eling.elcms.member.model.Member;
import com.eling.elcms.system.model.CommonUser;
import com.eling.elcms.travel.dao.ISignUpRegisterDao;
import com.eling.elcms.travel.model.*;
import com.eling.elcms.travel.model.view.SignUpRegisterView;
import org.springframework.stereotype.Repository;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
@Repository
public class SignUpRegisterDaoHibernate extends GenericDaoHibernate<SignUpRegister, Long> implements ISignUpRegisterDao{
	public SignUpRegisterDaoHibernate() {
		super(SignUpRegister.class);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<SignUpRegisterView> queryMember(Destination string, Date date, Date object) {
		SimpleDateFormat sdf =   new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String startDate ="";
		String endDate ="";
		if(date!=null){
			startDate= sdf.format(date);
		}
		if(object!=null){
			endDate= sdf.format(object);
		}
		StringBuffer sql = new StringBuffer();
		sql.append("	SELECT ");
		sql.append("		pla.pkPlanItem, ");
		sql.append("		pla.version, ");
		sql.append("		pla.startDate, ");
		sql.append("		pla.endDate, ");
		sql.append("		pla.limitmax, ");
		sql.append("		pla.limitmin, ");
		sql.append("		pla.maxpeople, ");
		sql.append("		pla.minPeople, ");
		sql.append("		pl.pkPlan, ");
		sql.append("		pl.`year`, ");
		sql.append("		pl.planTitle, ");
		sql.append("		pl.verNumber, ");
		sql.append("		d.pkDestination, ");
		sql.append("		d.`name` ");
		sql.append("	FROM ");
		sql.append("		travel_plan pl ");
		sql.append("	LEFT JOIN travel_plan_planitem pp ON pp.pkPlan = pl.pkPlan ");
		sql.append("	LEFT JOIN travel_planitem pla ON pp.pkPlanItem = pla.pkPlanItem ");
		sql.append("	LEFT JOIN travel_destination d ON d.pkDestination = pla.pkDestination ");
		sql.append("	WHERE ");
		sql.append("		pl.`status` = '"+Plan.Status.Release+"' ");
		if(string!=null){
			sql.append("	AND d.`name`='"+string.getName().toString()+"' ");
		}
		if(!startDate.isEmpty()&&!endDate.isEmpty()){
			sql.append("	AND pla.startDate BETWEEN '"+startDate+"' AND '"+endDate+"'  ");
		}
		sql.append("	AND pl.pkOrganization ="+((CommonUser)AppContext.curUser()).getOrganization().getPkOrganization()+"  ");
		List<Object[]> list=getSession().createNativeQuery(sql.toString()).list();
		List<SignUpRegisterView> returnlist=new ArrayList<SignUpRegisterView>();
		for(Object[] o:list){
			SignUpRegisterView surv=new SignUpRegisterView();
			surv.setPkPlanItem(o[0] == null ? null : Long.valueOf(o[0].toString()));
			surv.setVersion(o[1] == null ? null : Integer.valueOf(o[1].toString()));
			try {
				surv.setStartDate(o[2] == null ? null : sdf.parse(o[2].toString()));
				surv.setEndDate(o[3] == null ? null : sdf.parse(o[3].toString()));
				if(o[4]!=null&&(o[4].toString().equals("1") || o[4].toString().equals("true"))){
					surv.setLimitmax(true);
				}else{
					surv.setLimitmax(false);
				}
				if(o[5]!=null&&(o[5].toString().equals("1") || o[5].toString().equals("true"))){
					surv.setLimitmin(true);
				}else{
					surv.setLimitmin(false);
				}
				surv.setMaxpeople(o[6] == null ? null : Integer.valueOf(o[6].toString()));
				surv.setMinPeople(o[7] == null ? null : Integer.valueOf(o[7].toString()));
				surv.setYear(o[9] == null ? null : o[9].toString());
				surv.setName(o[10] == null ? null : o[10].toString());
				surv.setVerNumber(o[11] == null ? null : o[11].toString());
				surv.setPkDestination(o[12] == null ? null : Long.valueOf(o[12].toString()));
				surv.setDname(o[13] == null ? null : o[13].toString());
				surv.setCountMember(0);
				returnlist.add(surv);
			} catch (ParseException e) {
				e.printStackTrace();
			}
		}
		return returnlist;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<TravelContract> querycertificates(String s, SignUpRegister signUp,Long pkServicePoint) {
		StringBuffer sql = new StringBuffer();
		sql.append("	SELECT ");
		sql.append("		tcc.pkTravelContract,tcc.version as tv,tcc.memberCertificates,pi.`name` as piname,pi.idNumber, ");
		sql.append("		pi.pkPersonalInfo,pi.version as piv,p.`name` as pname,p.pkProduct,p.version as pv,pr.travelNum as travelNum ");
		sql.append("	FROM 	");
		sql.append("		travel_travelcontract tcc ");
		sql.append("	LEFT JOIN om_servicepoint sp ON tcc.pkServicePoint=sp.pkServicePoint ");
		sql.append("	LEFT JOIN mem_member mem ON tcc.pkMember=mem.pkMember ");
		sql.append("	LEFT JOIN bd_personalinfo pi ON mem.pkPersonalInfo=pi.pkPersonalInfo ");
		sql.append("	LEFT JOIN travel_productrule pr ON tcc.pkProductRule=pr.pkProductRule ");
		sql.append("	LEFT JOIN travel_product p ON pr.pkProduct=p.pkProduct ");
		sql.append("    WHERE ");
		sql.append("		tcc.memberCertificates LIKE '%"+s+"%' ");
		sql.append("	AND tcc.pkServicePoint="+pkServicePoint+" ");
		List<Object[]> list=getSession().createNativeQuery(sql.toString()).list();
		List<TravelContract> returnlist=new ArrayList<TravelContract>();
		for(Object[] o:list){
			TravelContract tc=new TravelContract();
			if(o[0]!=null){
				tc.setPkTravelContract(Long.valueOf(o[0].toString()));
			}
			if(o[1]!=null){
				tc.setVersion(Integer.valueOf(o[1].toString()));
			}
			if(o[2]!=null){
				tc.setMemberCertificates(o[2].toString());
			}
			Member mem=new Member();
			PersonalInfo pi=new PersonalInfo();
			if(o[5]!=null){
				pi.setPkPersonalInfo(Long.valueOf(o[5].toString()));
			}
			if(o[3]!=null){
				pi.setName(o[3].toString());
			}if(o[4]!=null){
				pi.setIdNumber(o[4].toString());
			}
			if(o[6]!=null){
				pi.setVersion(Integer.valueOf(o[6].toString()));
			}
			mem.setPersonalInfo(pi);
			tc.setMember(mem);
			ProductRule pr=new ProductRule();
			Product p=new Product();
			if(o[8]!=null){
				p.setPkProduct(Long.valueOf(o[8].toString()));
			}
			if(o[9]!=null){
				p.setVersion(Integer.valueOf(o[9].toString()));
			}
			if(o[10]!=null){
				pr.setTravelNum(Integer.valueOf(o[10].toString()));
			}
			if(o[7]!=null){
				p.setName(o[7].toString());
			}
			pr.setProduct(p);
			tc.setProductRule(pr);
			returnlist.add(tc);
		}
		return returnlist;
	}
	
}

